Excel 2010 Formulas by John Walkenbach

Author:John Walkenbach
Language: eng
Format: epub
Publisher: Wiley
Published: 2010-04-19T16:00:00+00:00

Part IV: Array Formulas

Chapter 14

Introducing Arrays

Chapter 15

Performing Magic with Array Formulas

Chapter 14: Introducing Arrays

In This Chapter

• The definition of an array and an array formula

• One-dimensional versus two-dimensional arrays

• How to work with array constants

• Techniques for working with array formulas

• Examples of multicell array formulas

• Examples of array formulas that occupy a single cell

One of Excel's most interesting (and most powerful) features is its ability to work with arrays in a formula. When you understand this concept, you'll be able to create elegant formulas that appear to perform magic. This chapter introduces the concept of arrays and is required reading for anyone who wants to become a master of Excel formulas. Chapter 15 continues with lots of useful examples.

Introducing Array Formulas

If you do any computer programming, you've probably been exposed to the concept of an array. An array is a collection of items operated on collectively or individually. In Excel, an array can be one-dimensional or two-dimensional. These dimensions correspond to rows and columns. For example, a one-dimensional array can be stored in a range that consists of one row (a horizontal array) or one column (a vertical array). A two-dimensional array can be stored in a rectangular range of cells. Excel doesn't support three-dimensional arrays (although its VBA programming language does).

As you'll see, though, arrays need not be stored in cells. You can also work with arrays that exist only in Excel's memory. You can then use an array formula to manipulate this information and return a result. An array formula can occupy multiple cells or reside in a single cell.

This section presents two array formula examples: an array formula that occupies multiple cells, and another array formula that occupies only one cell.

A multicell array formula

Figure 14-1 shows a simple worksheet set up to calculate product sales. Normally, you would calculate the value in column D (total sales per product) with a formula such as the one that follows, and then copy this formula down the column:

=B2*C2

After copying the formula, the worksheet contains six formulas in column D.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.